package com.investment.advisor.service.impl;

import com.investment.advisor.dto.FundDTO;
import com.investment.advisor.dto.TagDTO;
import com.investment.advisor.entity.Fund;
import com.investment.advisor.entity.Tag;
import com.investment.advisor.repository.FundRepository;
import com.investment.advisor.repository.TagRepository;
import com.investment.advisor.service.FundService;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityNotFoundException;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Optional;
import java.util.Set;
import java.util.stream.Collectors;

@Service
public class FundServiceImpl implements FundService {

    private final FundRepository fundRepository;
    private final TagRepository tagRepository;
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public FundServiceImpl(FundRepository fundRepository, TagRepository tagRepository, JdbcTemplate jdbcTemplate) {
        this.fundRepository = fundRepository;
        this.tagRepository = tagRepository;
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public List<FundDTO> getAllFunds() {
        String sql = "SELECT f.id, f.code, f.name, f.type, f.nav, f.daily_return, f.weekly_return, " +
                "f.monthly_return, f.yearly_return, f.size, f.manager, f.company, f.description, " +
                "f.rating, f.create_time, f.update_time " +
                "FROM funds f";
        
        List<FundDTO> fundDTOs = jdbcTemplate.query(sql, (rs, rowNum) -> mapRowToFundDTO(rs));
        
        // 为每个基金获取其标签
        for (FundDTO fundDTO : fundDTOs) {
            fundDTO.setTags(getTagsForFund(fundDTO.getId()));
        }
        
        return fundDTOs;
    }

    @Override
    public FundDTO getFundById(Long id) {
        String sql = "SELECT f.id, f.code, f.name, f.type, f.nav, f.daily_return, f.weekly_return, " +
                "f.monthly_return, f.yearly_return, f.size, f.manager, f.company, f.description, " +
                "f.rating, f.create_time, f.update_time " +
                "FROM funds f WHERE f.id = ?";
        
        try {
            FundDTO fundDTO = jdbcTemplate.queryForObject(sql, (rs, rowNum) -> mapRowToFundDTO(rs), id);
            if (fundDTO != null) {
                fundDTO.setTags(getTagsForFund(id));
                return fundDTO;
            }
            throw new EntityNotFoundException("Fund not found with id: " + id);
        } catch (Exception e) {
            throw new EntityNotFoundException("Fund not found with id: " + id);
        }
    }

    @Override
    public FundDTO getFundByCode(String code) {
        String sql = "SELECT f.id, f.code, f.name, f.type, f.nav, f.daily_return, f.weekly_return, " +
                "f.monthly_return, f.yearly_return, f.size, f.manager, f.company, f.description, " +
                "f.rating, f.create_time, f.update_time " +
                "FROM funds f WHERE f.code = ?";
        
        try {
            FundDTO fundDTO = jdbcTemplate.queryForObject(sql, (rs, rowNum) -> mapRowToFundDTO(rs), code);
            if (fundDTO != null) {
                fundDTO.setTags(getTagsForFund(fundDTO.getId()));
                return fundDTO;
            }
            throw new EntityNotFoundException("Fund not found with code: " + code);
        } catch (Exception e) {
            throw new EntityNotFoundException("Fund not found with code: " + code);
        }
    }

    @Override
    public List<FundDTO> searchFunds(String keyword) {
        String sql = "SELECT f.id, f.code, f.name, f.type, f.nav, f.daily_return, f.weekly_return, " +
                "f.monthly_return, f.yearly_return, f.size, f.manager, f.company, f.description, " +
                "f.rating, f.create_time, f.update_time " +
                "FROM funds f WHERE f.name LIKE ? OR f.code LIKE ?";
        
        List<FundDTO> fundDTOs = jdbcTemplate.query(
                sql, 
                (rs, rowNum) -> mapRowToFundDTO(rs),
                "%" + keyword + "%", 
                "%" + keyword + "%"
        );
        
        for (FundDTO fundDTO : fundDTOs) {
            fundDTO.setTags(getTagsForFund(fundDTO.getId()));
        }
        
        return fundDTOs;
    }

    @Override
    public List<FundDTO> getFundsByType(String type) {
        String sql = "SELECT f.id, f.code, f.name, f.type, f.nav, f.daily_return, f.weekly_return, " +
                "f.monthly_return, f.yearly_return, f.size, f.manager, f.company, f.description, " +
                "f.rating, f.create_time, f.update_time " +
                "FROM funds f WHERE f.type = ?";
        
        List<FundDTO> fundDTOs = jdbcTemplate.query(sql, (rs, rowNum) -> mapRowToFundDTO(rs), type);
        
        for (FundDTO fundDTO : fundDTOs) {
            fundDTO.setTags(getTagsForFund(fundDTO.getId()));
        }
        
        return fundDTOs;
    }

    @Override
    public List<FundDTO> getFundsByRating(Integer rating) {
        String sql = "SELECT f.id, f.code, f.name, f.type, f.nav, f.daily_return, f.weekly_return, " +
                "f.monthly_return, f.yearly_return, f.size, f.manager, f.company, f.description, " +
                "f.rating, f.create_time, f.update_time " +
                "FROM funds f WHERE f.rating >= ?";
        
        List<FundDTO> fundDTOs = jdbcTemplate.query(sql, (rs, rowNum) -> mapRowToFundDTO(rs), rating);
        
        for (FundDTO fundDTO : fundDTOs) {
            fundDTO.setTags(getTagsForFund(fundDTO.getId()));
        }
        
        return fundDTOs;
    }

    @Override
    public List<FundDTO> getFundsByTypeAndRating(String type, Integer rating) {
        String sql = "SELECT f.id, f.code, f.name, f.type, f.nav, f.daily_return, f.weekly_return, " +
                "f.monthly_return, f.yearly_return, f.size, f.manager, f.company, f.description, " +
                "f.rating, f.create_time, f.update_time " +
                "FROM funds f WHERE f.type = ? AND f.rating >= ?";
        
        List<FundDTO> fundDTOs = jdbcTemplate.query(sql, (rs, rowNum) -> mapRowToFundDTO(rs), type, rating);
        
        for (FundDTO fundDTO : fundDTOs) {
            fundDTO.setTags(getTagsForFund(fundDTO.getId()));
        }
        
        return fundDTOs;
    }

    @Override
    public List<FundDTO> getFundsByTagName(String tagName) {
        String sql = "SELECT DISTINCT f.id, f.code, f.name, f.type, f.nav, f.daily_return, f.weekly_return, " +
                "f.monthly_return, f.yearly_return, f.size, f.manager, f.company, f.description, " +
                "f.rating, f.create_time, f.update_time " +
                "FROM funds f " +
                "JOIN fund_tag ft ON f.id = ft.fund_id " +
                "JOIN tags t ON ft.tag_id = t.id " +
                "WHERE t.name = ?";
        
        List<FundDTO> fundDTOs = jdbcTemplate.query(sql, (rs, rowNum) -> mapRowToFundDTO(rs), tagName);
        
        for (FundDTO fundDTO : fundDTOs) {
            fundDTO.setTags(getTagsForFund(fundDTO.getId()));
        }
        
        return fundDTOs;
    }

    @Override
    public List<FundDTO> getTopPerformingFunds(int limit) {
        String sql = "SELECT f.id, f.code, f.name, f.type, f.nav, f.daily_return, f.weekly_return, " +
                "f.monthly_return, f.yearly_return, f.size, f.manager, f.company, f.description, " +
                "f.rating, f.create_time, f.update_time " +
                "FROM funds f " +
                "ORDER BY f.yearly_return DESC LIMIT ?";
        
        List<FundDTO> fundDTOs = jdbcTemplate.query(sql, (rs, rowNum) -> mapRowToFundDTO(rs), limit);
        
        for (FundDTO fundDTO : fundDTOs) {
            fundDTO.setTags(getTagsForFund(fundDTO.getId()));
        }
        
        return fundDTOs;
    }

    @Override
    @Transactional
    public FundDTO createFund(FundDTO fundDTO) {
        Fund fund = convertToEntity(fundDTO);
        Fund savedFund = fundRepository.save(fund);
        return convertEntityToDTO(savedFund);
    }

    @Override
    @Transactional
    public FundDTO updateFund(Long id, FundDTO fundDTO) {
        Optional<Fund> existingFundOpt = fundRepository.findById(id);
        
        if (existingFundOpt.isPresent()) {
            Fund existingFund = existingFundOpt.get();
            
            // 不要覆盖这些字段
            Set<Tag> existingTags = existingFund.getTags();
            
            // 复制除id和tags之外的所有属性
            BeanUtils.copyProperties(fundDTO, existingFund, "id", "tags", "createTime");
            
            // 如果DTO中有标签，则处理标签
            if (fundDTO.getTags() != null && !fundDTO.getTags().isEmpty()) {
                Set<Tag> updatedTags = new HashSet<>();
                
                for (TagDTO tagDTO : fundDTO.getTags()) {
                    if (tagDTO.getId() != null) {
                        tagRepository.findById(tagDTO.getId())
                                .ifPresent(updatedTags::add);
                    } else if (tagDTO.getName() != null) {
                        tagRepository.findByName(tagDTO.getName())
                                .ifPresent(updatedTags::add);
                    }
                }
                
                // 如果找到了标签，则设置它们
                if (!updatedTags.isEmpty()) {
                    existingFund.setTags(updatedTags);
                } else {
                    existingFund.setTags(existingTags);  // 保持原有标签
                }
            } else {
                existingFund.setTags(existingTags);  // 保持原有标签
            }
            
            Fund updatedFund = fundRepository.save(existingFund);
            return convertEntityToDTO(updatedFund);
        } else {
            throw new EntityNotFoundException("Fund not found with id: " + id);
        }
    }

    @Override
    @Transactional
    public void deleteFund(Long id) {
        if (fundRepository.existsById(id)) {
            fundRepository.deleteById(id);
        } else {
            throw new EntityNotFoundException("Fund not found with id: " + id);
        }
    }

    @Override
    @Transactional
    public void addTagToFund(Long fundId, Long tagId) {
        Fund fund = fundRepository.findById(fundId)
                .orElseThrow(() -> new EntityNotFoundException("Fund not found with id: " + fundId));
                
        Tag tag = tagRepository.findById(tagId)
                .orElseThrow(() -> new EntityNotFoundException("Tag not found with id: " + tagId));
                
        Set<Tag> tags = fund.getTags();
        if (tags == null) {
            tags = new HashSet<>();
        }
        tags.add(tag);
        fund.setTags(tags);
        
        fundRepository.save(fund);
    }

    @Override
    @Transactional
    public void removeTagFromFund(Long fundId, Long tagId) {
        Fund fund = fundRepository.findById(fundId)
                .orElseThrow(() -> new EntityNotFoundException("Fund not found with id: " + fundId));
                
        Tag tag = tagRepository.findById(tagId)
                .orElseThrow(() -> new EntityNotFoundException("Tag not found with id: " + tagId));
                
        Set<Tag> tags = fund.getTags();
        if (tags != null) {
            tags.remove(tag);
            fund.setTags(tags);
            fundRepository.save(fund);
        }
    }
    
    // 将ResultSet映射到FundDTO
    private FundDTO mapRowToFundDTO(ResultSet rs) throws SQLException {
        FundDTO dto = new FundDTO();
        dto.setId(rs.getLong("id"));
        dto.setCode(rs.getString("code"));
        dto.setName(rs.getString("name"));
        dto.setType(rs.getString("type"));
        
        // 处理可能为null的数值类型
        BigDecimal nav = rs.getBigDecimal("nav");
        if (rs.wasNull()) nav = null;
        dto.setNav(nav);
        
        BigDecimal dailyReturn = rs.getBigDecimal("daily_return");
        if (rs.wasNull()) dailyReturn = null;
        dto.setDailyReturn(dailyReturn);
        
        BigDecimal weeklyReturn = rs.getBigDecimal("weekly_return");
        if (rs.wasNull()) weeklyReturn = null;
        dto.setWeeklyReturn(weeklyReturn);
        
        BigDecimal monthlyReturn = rs.getBigDecimal("monthly_return");
        if (rs.wasNull()) monthlyReturn = null;
        dto.setMonthlyReturn(monthlyReturn);
        
        BigDecimal yearlyReturn = rs.getBigDecimal("yearly_return");
        if (rs.wasNull()) yearlyReturn = null;
        dto.setYearlyReturn(yearlyReturn);
        
        BigDecimal size = rs.getBigDecimal("size");
        if (rs.wasNull()) size = null;
        dto.setSize(size);
        
        dto.setManager(rs.getString("manager"));
        dto.setCompany(rs.getString("company"));
        dto.setDescription(rs.getString("description"));
        
        Integer rating = rs.getInt("rating");
        if (rs.wasNull()) rating = null;
        dto.setRating(rating);
        
        dto.setCreateTime(rs.getTimestamp("create_time") != null ? 
            rs.getTimestamp("create_time").toLocalDateTime() : null);
        dto.setUpdateTime(rs.getTimestamp("update_time") != null ? 
            rs.getTimestamp("update_time").toLocalDateTime() : null);
            
        return dto;
    }
    
    // 获取基金的标签
    private List<TagDTO> getTagsForFund(Long fundId) {
        String sql = "SELECT t.id, t.name, t.category, t.description, t.create_time, t.update_time " +
                "FROM tags t " +
                "JOIN fund_tag ft ON t.id = ft.tag_id " +
                "WHERE ft.fund_id = ?";
        
        return jdbcTemplate.query(sql, (rs, rowNum) -> {
            TagDTO dto = new TagDTO();
            dto.setId(rs.getLong("id"));
            dto.setName(rs.getString("name"));
            dto.setCategory(rs.getString("category"));
            dto.setDescription(rs.getString("description"));
            dto.setCreateTime(rs.getTimestamp("create_time") != null ? 
                rs.getTimestamp("create_time").toLocalDateTime() : null);
            dto.setUpdateTime(rs.getTimestamp("update_time") != null ? 
                rs.getTimestamp("update_time").toLocalDateTime() : null);
            return dto;
        }, fundId);
    }
    
    // 将实体转换为DTO（安全版本）
    private FundDTO convertEntityToDTO(Fund fund) {
        if (fund == null) return null;
        
        FundDTO dto = new FundDTO();
        dto.setId(fund.getId());
        dto.setCode(fund.getCode());
        dto.setName(fund.getName());
        dto.setType(fund.getType());
        dto.setNav(fund.getNav());
        dto.setDailyReturn(fund.getDailyReturn());
        dto.setWeeklyReturn(fund.getWeeklyReturn());
        dto.setMonthlyReturn(fund.getMonthlyReturn());
        dto.setYearlyReturn(fund.getYearlyReturn());
        dto.setSize(fund.getSize());
        dto.setManager(fund.getManager());
        dto.setCompany(fund.getCompany());
        dto.setDescription(fund.getDescription());
        dto.setRating(fund.getRating());
        dto.setCreateTime(fund.getCreateTime());
        dto.setUpdateTime(fund.getUpdateTime());
        
        // 处理标签
        if (fund.getTags() != null && !fund.getTags().isEmpty()) {
            List<TagDTO> tagDtos = new ArrayList<>();
            for (Tag tag : fund.getTags()) {
                TagDTO tagDto = new TagDTO();
                tagDto.setId(tag.getId());
                tagDto.setName(tag.getName());
                tagDto.setCategory(tag.getCategory());
                tagDto.setDescription(tag.getDescription());
                tagDto.setCreateTime(tag.getCreateTime());
                tagDto.setUpdateTime(tag.getUpdateTime());
                tagDtos.add(tagDto);
            }
            dto.setTags(tagDtos);
        }
        
        return dto;
    }

    // 将DTO转换为实体
    private Fund convertToEntity(FundDTO fundDTO) {
        Fund fund = new Fund();
        BeanUtils.copyProperties(fundDTO, fund, "id", "tags", "createTime", "updateTime");
        
        // 处理标签
        if (fundDTO.getTags() != null) {
            Set<Tag> tags = new HashSet<>();
            for (TagDTO tagDTO : fundDTO.getTags()) {
                if (tagDTO.getId() != null) {
                    tagRepository.findById(tagDTO.getId())
                            .ifPresent(tags::add);
                } else if (tagDTO.getName() != null) {
                    tagRepository.findByName(tagDTO.getName())
                            .ifPresent(tags::add);
                }
            }
            fund.setTags(tags);
        }
        
        return fund;
    }
} 